from typing import Optional, List
from sqlmodel import SQLModel, Field, create_engine, Session, select, Relationship


class HeroCreateModel(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None


# 1. Define Model
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    messages: List["Message"] = Relationship(back_populates="hero")


class Message(SQLModel, table=True):
    __tablename__ = "user_message"
    __table_args__ = {"comment": "用户发的消息"}
    id: Optional[int] = Field(default=None, primary_key=True)
    hero_id: Optional[int] = Field(foreign_key="hero.id")
    hero: Optional[Hero] = Relationship(back_populates="messages")
    msg_title: Optional[str] = Field(description="标题")
    msg_content: Optional[str] = Field(description="内容")


# 2. Create Database Engine
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url)


# 3. Create Database and Tables
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


# 4. CRUD Operations
class HeroCRUD:
    # Create
    @staticmethod
    def create_hero(name: str, secret_name: str, age: Optional[int] = None):
        with Session(engine) as session:
            # 方法一：先创建message,然后创建hero时，保存heros
            mess = Message(msg_title="First message", msg_content="Hi there!")
            mess2 = Message(msg_title="Second message", msg_content="Another message")
            hero = Hero(
                name=name, secret_name=secret_name, age=age, messages=[mess, mess2]
            )
            session.add(hero)
            session.commit()
            session.refresh(hero)

            # 方法二：先创建hero,然后创建message,最后保存Message
            hero3 = Hero(name=name + "_2", secret_name=secret_name + "_2", age=age)
            message3 = Message(
                msg_title="第3条 message", msg_content="Hi 第3条there!", hero=hero3
            )
            message4 = Message(
                msg_title="第四条 message", msg_content="Hi 第4条 there!", hero=hero3
            )
            session.add(message3)
            session.add(message4)
            # session.add(hero3) #这个应不用。会自动
            session.commit()
            session.refresh(hero3)
            return hero

    # Read (single)
    @staticmethod
    def get_hero(hero_id: int):
        with Session(engine) as session:
            hero = session.get(Hero, hero_id)
            return hero

    # Read (all)
    @staticmethod
    def get_all_heroes():
        with Session(engine) as session:
            statement = select(Hero)
            heroes = session.exec(statement).all()
            return heroes

    # Update
    @staticmethod
    def update_hero(
        hero_id: int,
        name: Optional[str] = None,
        secret_name: Optional[str] = None,
        age: Optional[int] = None,
    ):
        with Session(engine) as session:
            hero = session.get(Hero, hero_id)
            if not hero:
                return None

            if name is not None:
                hero.name = name
            if secret_name is not None:
                hero.secret_name = secret_name
            if age is not None:
                hero.age = age

            session.add(hero)
            session.commit()
            session.refresh(hero)
            return hero

    # Delete
    @staticmethod
    def delete_hero(hero_id: int):
        with Session(engine) as session:
            hero = session.get(Hero, hero_id)
            if not hero:
                return False

            session.delete(hero)
            session.commit()
            return True


# 5. Main Function to Demonstrate Usage
def main():
    # Create tables
    create_db_and_tables()

    def _create_heros():

        hero1 = HeroCRUD.create_hero(name="Deadpond", secret_name="Dive Wilson")
        hero2 = HeroCRUD.create_hero(
            name="Spider-Boy", secret_name="Pedro Parqueador", age=16
        )
        hero3 = HeroCRUD.create_hero(
            name="Rusty-Man", secret_name="Tommy Sharp", age=48
        )
        print("Created heroes:")
        print(hero1)
        print(hero2)
        print(hero3)

    def _print_heros():
        print("\nAll heroes:")
        heroes = HeroCRUD.get_all_heroes()
        for hero in heroes:
            print(hero)

    # Get single hero
    print("\nGet single hero:")
    hero = HeroCRUD.get_hero(1)
    print(hero)

    # Update hero
    print("\nUpdate hero:")
    updated_hero = HeroCRUD.update_hero(1, name="Deadpool", age=35)
    print(updated_hero)

    # Delete hero
    print("\nDelete hero:")
    success = HeroCRUD.delete_hero(2)
    print(f"Deleted hero with id {2}: {success}")

    _create_heros()
    _print_heros()


if __name__ == "__main__":
    # main()
    with Session(engine) as session:
        hero_date = HeroCreateModel(name="ww", secret_name="rico chen Wilson")
        hero: Hero = Hero.model_validate(hero_date)
        print(hero.name)
        session.add(hero)
        session.commit()
        session.refresh(hero)
        print(hero)
